package com.gyh.modules.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class handleExcel {
	protected static Logger log = LoggerFactory.getLogger(handleExcel.class);
	
    /**
     * 源文件路径
     */
    private final static String dataSourcePath      = "C:/Users/Administrator/Desktop/data/main.xlsx";
    private final static String config_farmPath     = "C:/Users/Administrator/Desktop/data/config_farm.xlsx";
    private final static String config_buildingPath = "C:/Users/Administrator/Desktop/data/config_building.xlsx";
    private final static String hatch_mainPath      = "C:/Users/Administrator/Desktop/data/hatch_main.xlsx";
    private final static String hatch_penPath       = "C:/Users/Administrator/Desktop/data/hatch_pen.xlsx";
    private final static String chickenPath         = "C:/Users/Administrator/Desktop/data/chicken.xlsx";
    private final static String chicken_cage_wingPath = "C:/Users/Administrator/Desktop/data/chicken_cage_wing.xlsx";
    private final static String config_chicken_use = "C:/Users/Administrator/Desktop/data/config_chicken_use.xlsx";

    /**
     * 数据源
     */
    private static ArrayList<String> identifiers = new ArrayList<String>();
    private static ArrayList<String> haxs        = new ArrayList<String>();
    private static ArrayList<String> cages       = new ArrayList<String>();
    private static ArrayList<String> wbs         = new ArrayList<String>();
    private static ArrayList<String> sids        = new ArrayList<String>();
    private static ArrayList<String> dids        = new ArrayList<String>();
    private static ArrayList<String> pens        = new ArrayList<String>();
    private static ArrayList<String> smallPens   = new ArrayList<String>();
    private static ArrayList<String> realSPens   = new ArrayList<String>();
    private static ArrayList<String> penIds      = new ArrayList<String>();
    private static ArrayList<String> uses        = new ArrayList<String>();
    private static ArrayList<Double> sexs        = new ArrayList<Double>();
    private static ArrayList<Double> farms       = new ArrayList<Double>();
    private static ArrayList<Double> builds      = new ArrayList<Double>();

    public static void main(String[] args) throws IOException {
        try {
            handleDatasource(dataSourcePath);
            log.info("main方法。。。。。");
        } catch (IOException e) {
            e.printStackTrace();
        }

        setupHatchMainExcel();
        setupChickenCageWing();
        setupHatchPen();
        setupChickenExcel();
    }

    /**
     * 处理数据源
     * @param path  数据源地址
     * @throws IOException
     */
    private static void handleDatasource(String path) throws IOException {
    	log.info("handleDatasource方法。。。。。");
        XSSFWorkbook wb = getWorkbook(path);
        XSSFSheet sheet = wb.getSheetAt(0);

        for(int i = 1; i <= sheet.getLastRowNum(); i ++) {
        	log.info("===============>"+i);
            XSSFRow srcRow = sheet.getRow(i);
            // 身份号
            XSSFCell srcw = srcRow.getCell(0);
            XSSFCell srcRowCell = srcRow.getCell(1);
            String identifier = srcRowCell.getStringCellValue();
            String hax = StringUtils.left(identifier, 6);
            if (!identifiers.contains(identifier)) {
                identifiers.add(identifier);
            }
            if (!haxs.contains(hax)) {
                haxs.add(hax);
            }

            // 公母
            XSSFCell srcSexCell = srcRow.getCell(2);
            Double sex          = srcSexCell.getNumericCellValue();
            sexs.add(Math.floor(sex));

            // 翅号
            XSSFCell srcWbCell = srcRow.getCell(3);
            String wbNum       = srcWbCell.getStringCellValue();
            wbs.add(wbNum);

            // 场号
            XSSFCell srcFarmCell = srcRow.getCell(4);
            Double farm          = srcFarmCell.getNumericCellValue();
            farms.add(farm);

            // 栋号
            XSSFCell srcBuildCell = srcRow.getCell(5);
            Double build          = srcBuildCell.getNumericCellValue();
            builds.add(build);

            // 笼号
            XSSFCell srcCageCell = srcRow.getCell(6);
            srcCageCell.setCellType(CellType.STRING);
            String cage = srcCageCell.getStringCellValue();
            cages.add(cage);

            // 家系
            XSSFCell srcPenCell = srcRow.getCell(15);
            String pen          = srcPenCell.getStringCellValue();

            // 小家系
            XSSFCell srcSmallPenCell = srcRow.getCell(16);
            String smallPen          = srcSmallPenCell.getStringCellValue();
            realSPens.add(smallPen);

            // 父亲id
            XSSFCell srcSidCell = srcRow.getCell(10);
            String sid          = srcSidCell.getStringCellValue();

            // 母亲id(唯一)
            XSSFCell srcDidCell = srcRow.getCell(11);
            String did          = srcDidCell.getStringCellValue();
            if (!dids.contains(did)) {
                dids.add(did);
                sids.add(sid);
                smallPens.add(smallPen);
                pens.add(pen);
                String penId = srcRow.getCell(1).getStringCellValue();
                penIds.add(penId);
            }

            // 用途
            XSSFCell srcUsePenCell = srcRow.getCell(17);
            String use = srcUsePenCell.getStringCellValue();
            uses.add(use);

        }
    }

    /**
     * 产生hatch_main表
     * @throws IOException
     */
    private static void setupHatchMainExcel () throws IOException {
    	log.info("setupHatchMainExcel方法。。。。。");
        XSSFWorkbook hatch_main_wb = getWorkbook(hatch_mainPath);
        XSSFSheet hatch_main_sheet = hatch_main_wb.getSheetAt(0);
        hatch_main_sheet.setColumnWidth(0,10000);
        hatch_main_sheet.setColumnWidth(3,7000);
        hatch_main_sheet.setColumnWidth(4,10000);
        hatch_main_sheet.setColumnWidth(14,7000);
        hatch_main_sheet.setColumnWidth(16,7000);
        hatch_main_sheet.setColumnWidth(17,7000);

        for (int i = 0; i < haxs.size(); i ++) {
            XSSFRow hatch_main_row = hatch_main_sheet.createRow(i+1);
            String hax = haxs.get(i);
            String use = uses.get(i);

            // id
            UUID uuid= UUID.randomUUID();
            XSSFCell uuidCell = hatch_main_row.createCell(0);
            uuidCell.setCellValue(uuid.toString().replace("-", ""));

            // strain
            XSSFCell strainCell = hatch_main_row.createCell(1);
            strainCell.setCellValue(hax.substring(0,2));

            // gen
            XSSFCell genCell = hatch_main_row.createCell(2);
            genCell.setCellValue(hax.substring(2,4));

            // hax
            XSSFCell haxCell = hatch_main_row.createCell(3);
            haxCell.setCellValue(hax);

            // use_id
            XSSFCell useCell = hatch_main_row.createCell(4);
            XSSFWorkbook config_chicken_use_wb = getWorkbook(config_chicken_use);
            XSSFSheet config_chicken_use_sheet = config_chicken_use_wb.getSheetAt(0);
            for (int j = 1; j <= config_chicken_use_sheet.getLastRowNum(); j ++) {
                XSSFRow row = config_chicken_use_sheet.getRow(j);
                XSSFCell cell = row.getCell(1);
                if (cell.getStringCellValue().equals(use)) {
                    useCell.setCellValue(row.getCell(0).getStringCellValue());
                }
            }

            // create_by
            XSSFCell createByCell = hatch_main_row.createCell(17);
            createByCell.setCellValue("1");

            // create_date
            XSSFCell createDateCell = hatch_main_row.createCell(18);
            createDateCell.setCellValue(currentDateStr());

            // update_by
            XSSFCell updateByCell = hatch_main_row.createCell(19);
            updateByCell.setCellValue("1");

            // update_date
            XSSFCell updateDateCell = hatch_main_row.createCell(20);
            updateDateCell.setCellValue(currentDateStr());

            // remarks
            XSSFCell remarkCell = hatch_main_row.createCell(21);
            remarkCell.setCellValue("期初导入");

            // del_flag
            XSSFCell delflagCell = hatch_main_row.createCell(22);
            delflagCell.setCellValue("0");
        }
        outputWorkbook(hatch_mainPath,hatch_main_wb);
    }

    /**
     * 产生chicken表
     * @throws IOException
     */
    private static void setupChickenExcel() throws IOException {
    	log.info("setupChickenExcel方法。。。。。");
        XSSFWorkbook chicken_wb = getWorkbook(chickenPath);
        XSSFSheet chicken_sheet = chicken_wb.getSheetAt(0);
        chicken_sheet.setColumnWidth(0,10000);
        chicken_sheet.setColumnWidth(1,10000);
        chicken_sheet.setColumnWidth(2,7000);
        chicken_sheet.setColumnWidth(3,7000);
        chicken_sheet.setColumnWidth(10,7000);
        chicken_sheet.setColumnWidth(12,7000);
        chicken_sheet.setColumnWidth(13,7000);

        for (int i = 0; i < identifiers.size(); i ++) {
            XSSFRow chicken_row = chicken_sheet.createRow(i+1);

            String small_pen    = realSPens.get(i);
            String identity_num = identifiers.get(i);
            String hax          = identity_num.substring(0,6);
            String wb           = wbs.get(i);
            Double sex          = sexs.get(i);
            // id
            UUID uuid= UUID.randomUUID();
            XSSFCell uuidCell = chicken_row.createCell(0);
            uuidCell.setCellValue(uuid.toString().replace("-", ""));

            // hatchpen_id
            XSSFCell hmidCell = chicken_row.createCell(1);
            XSSFWorkbook hatch_pen_wb = getWorkbook(hatch_penPath);
            XSSFSheet hatch_pen_sheet = hatch_pen_wb.getSheetAt(0);
            for (int j = 1; j <= hatch_pen_sheet.getLastRowNum(); j ++) {
                XSSFRow hatch_pen_sheetRow = hatch_pen_sheet.getRow(j);
                // 小家系
                XSSFCell smallPenCell = hatch_pen_sheetRow.getCell(3);
                if (smallPenCell.getStringCellValue().equals(small_pen)) {
                    // 通过小家系获取到家系的id
                    hmidCell.setCellValue(hatch_pen_sheetRow.getCell(0).getStringCellValue());
                }
            }
            
            // wb
            XSSFCell smallPenCell = chicken_row.createCell(2);
            smallPenCell.setCellValue(small_pen);
            
            // wb
            XSSFCell wbCell = chicken_row.createCell(3);
            wbCell.setCellValue(wb);

            // identity_number
            XSSFCell haxCell = chicken_row.createCell(4);
            haxCell.setCellValue(identity_num);

            // sex
            XSSFCell sexCell = chicken_row.createCell(5);
            sexCell.setCellValue(sex);
            
            //state
            XSSFCell stateCell = chicken_row.createCell(6);
            stateCell.setCellValue(0);

            // create_by
            XSSFCell createByCell = chicken_row.createCell(11);
            createByCell.setCellValue("1");

            // create_date
            XSSFCell createDateCell = chicken_row.createCell(12);
            createDateCell.setCellValue(currentDateStr());

            // update_by
            XSSFCell updateByCell = chicken_row.createCell(13);
            updateByCell.setCellValue("1");

            // update_date
            XSSFCell updateDateCell = chicken_row.createCell(14);
            updateDateCell.setCellValue(currentDateStr());

            // remark
            XSSFCell remarkCell = chicken_row.createCell(15);
            remarkCell.setCellValue("期初导入");

            // del_flag
            XSSFCell delflagCell = chicken_row.createCell(16);
            delflagCell.setCellValue("0");
        }
        outputWorkbook(chickenPath,chicken_wb);
    }

    /**
     * 产生chicken_cage_wing表
     * @throws IOException
     */
    private static void setupChickenCageWing() throws IOException {
    	log.info("setupChickenCageWing方法。。。。。");
        XSSFWorkbook chicken_cage_wing_wb = getWorkbook(chicken_cage_wingPath);
        XSSFSheet chicken_cage_wing_sheet = chicken_cage_wing_wb.getSheetAt(0);
        chicken_cage_wing_sheet.setColumnWidth(0,10000);
        chicken_cage_wing_sheet.setColumnWidth(1,10000);
        chicken_cage_wing_sheet.setColumnWidth(2,5000);
        chicken_cage_wing_sheet.setColumnWidth(3,5000);
        chicken_cage_wing_sheet.setColumnWidth(5,7000);
        chicken_cage_wing_sheet.setColumnWidth(7,7000);
        chicken_cage_wing_sheet.setColumnWidth(8,7000);

        for (int i = 0; i < identifiers.size(); i ++) {
            XSSFRow chicken_cage_wing_row = chicken_cage_wing_sheet.createRow(i+1);

            String identity_num = identifiers.get(i);
            String cage = cages.get(i);
            Double farm = farms.get(i);
            Double build = builds.get(i);
            Integer intBuild = build.intValue();

            // 1. id
            UUID uuid= UUID.randomUUID();
            XSSFCell uuidCell = chicken_cage_wing_row.createCell(0);
            uuidCell.setCellValue(uuid.toString().replace("-", ""));

            // 2. building_number
            XSSFCell buildingCell = chicken_cage_wing_row.createCell(1);

            // 2.1 获取config_farm表的第一个sheet
            XSSFWorkbook config_farm_wb = getWorkbook(config_farmPath);
            XSSFSheet config_farm_sheet = config_farm_wb.getSheetAt(0);

            // 2.2 获取config_building表的第一个sheet
            XSSFWorkbook config_building_wb = getWorkbook(config_buildingPath);
            XSSFSheet config_build_sheet = config_building_wb.getSheetAt(0);

            for (int j = 1; j <= config_farm_sheet.getLastRowNum(); j ++) {
                XSSFRow farmRow = config_farm_sheet.getRow(j);
                XSSFCell farmCodeCell = farmRow.getCell(2);
                if (farmCodeCell.getNumericCellValue() == farm) {
                    // 获取config_farm中code对应的farmId
                    String farmId = farmRow.getCell(0).getStringCellValue();
                    // 从config_building表中用farmId和name唯一确定一栋，并取到该栋id
                    for (int k = 1; k <= config_build_sheet.getLastRowNum(); k ++){
                        XSSFRow buildRow = config_build_sheet.getRow(k);
                        XSSFCell buildFarmIdCell = buildRow.getCell(1);
                        XSSFCell buildNameCell = buildRow.getCell(2);
                        buildNameCell.setCellType(CellType.STRING);
                        String buildFarmId = buildFarmIdCell.getStringCellValue();
                        String buildName = buildNameCell.getStringCellValue();
                        if (buildFarmId.equals(farmId) && buildName.equals((intBuild.toString() + "栋"))) {
                            buildingCell.setCellValue(buildRow.getCell(0).getStringCellValue());
                        }
                    }
                }
            }
            
            //当只是生成四张表时(如果生成多个，就不需要这个数据了)
            buildingCell.setCellValue("3c39db8d534840919aa2ead872b278b5");

            // 3. cage_number
            XSSFCell cageCell = chicken_cage_wing_row.createCell(2);
            cageCell.setCellValue(cage);

            // 4. identity_number
            XSSFCell haxCell = chicken_cage_wing_row.createCell(3);
            haxCell.setCellValue(identity_num);

            // create_by
            XSSFCell createByCell = chicken_cage_wing_row.createCell(4);
            createByCell.setCellValue("1");

            // create_date
            XSSFCell createDateCell = chicken_cage_wing_row.createCell(5);
            createDateCell.setCellValue(currentDateStr());

            // update_by
            XSSFCell updateByCell = chicken_cage_wing_row.createCell(6);
            updateByCell.setCellValue("1");

            // update_date
            XSSFCell updateDateCell = chicken_cage_wing_row.createCell(7);
            updateDateCell.setCellValue(currentDateStr());
            // remark
            XSSFCell remarkCell = chicken_cage_wing_row.createCell(8);
            remarkCell.setCellValue("期初导入");

            // 5. del_flag
            XSSFCell delflagCell = chicken_cage_wing_row.createCell(9);
            delflagCell.setCellValue("0");
        }
        outputWorkbook(chicken_cage_wingPath,chicken_cage_wing_wb);

    }

    /**
     * 产生hatch_pen表
     * @throws IOException
     */
    private static void setupHatchPen() throws IOException {
    	log.info("setupHatchPen方法。。。。。");
        XSSFWorkbook hatch_pen_wb = getWorkbook(hatch_penPath);
        XSSFSheet hatch_pen_sheet = hatch_pen_wb.getSheetAt(0);
        hatch_pen_sheet.setColumnWidth(0,10000);
        hatch_pen_sheet.setColumnWidth(1,10000);
        hatch_pen_sheet.setColumnWidth(4,7000);
        hatch_pen_sheet.setColumnWidth(5,7000);
        hatch_pen_sheet.setColumnWidth(21,7000);
        hatch_pen_sheet.setColumnWidth(23,7000);
        hatch_pen_sheet.setColumnWidth(24,7000);
        for (int i = 0; i < smallPens.size(); i ++) {
            String smallPen = smallPens.get(i);
            String pen = pens.get(i);
            String sid = sids.get(i);
            String did = dids.get(i);
            String penHax = penIds.get(i).substring(0,6);

            XSSFRow hatchpen_row = hatch_pen_sheet.createRow(i+1);

            // id
            UUID uuid= UUID.randomUUID();
            XSSFCell uuidCell = hatchpen_row.createCell(0);
            uuidCell.setCellValue(uuid.toString().replace("-", ""));

            // hm_id
            XSSFCell hmidCell = hatchpen_row.createCell(1);
            // 获取hatch_main表的第一个sheet
            XSSFWorkbook hatch_main_wb = getWorkbook(hatch_mainPath);
            XSSFSheet hatch_main_sheet = hatch_main_wb.getSheetAt(0);

            for (int j = 1; j <= hatch_main_sheet.getLastRowNum(); j ++) {
                XSSFRow hatchMainRow = hatch_main_sheet.getRow(j);
                // 取得批次号
                XSSFCell haxCell = hatchMainRow.getCell(3);
                if (haxCell.getStringCellValue().equals(penHax)) {
                    // 将批次号对应的id赋值给hatch_pen的hm_id
                    hmidCell.setCellValue(hatchMainRow.getCell(0).getStringCellValue());
                }
            }

            // pen
            XSSFCell penCell = hatchpen_row.createCell(2);
            penCell.setCellValue(pen);

            // small_pen
            XSSFCell smallPenCell = hatchpen_row.createCell(3);
            smallPenCell.setCellValue(smallPen);

            // s_id
            XSSFCell sidCell = hatchpen_row.createCell(4);
            sidCell.setCellValue(sid);

            // d_id
            XSSFCell didCell = hatchpen_row.createCell(5);
            didCell.setCellValue(did);
            
            XSSFCell Cell6 = hatchpen_row.createCell(6);
            Cell6.setCellValue(5);
            XSSFCell Cell7 = hatchpen_row.createCell(7);
            Cell7.setCellValue(5);
            XSSFCell Cell8 = hatchpen_row.createCell(8);
            Cell8.setCellValue(5);
            XSSFCell Cell9 = hatchpen_row.createCell(9);
            Cell9.setCellValue(5);
            XSSFCell Cell10 = hatchpen_row.createCell(10);
            Cell10.setCellValue(5);
            XSSFCell Cell11 = hatchpen_row.createCell(11);
            Cell11.setCellValue(5);
            XSSFCell Cell12 = hatchpen_row.createCell(12);
            Cell12.setCellValue(5);
            XSSFCell Cell13 = hatchpen_row.createCell(13);
            Cell13.setCellValue(5);
            XSSFCell Cell14 = hatchpen_row.createCell(14);
            Cell14.setCellValue(5);
            XSSFCell Cell15 = hatchpen_row.createCell(15);
            Cell15.setCellValue(1);
            XSSFCell Cell16 = hatchpen_row.createCell(16);
            Cell16.setCellValue(5);
            XSSFCell Cell17 = hatchpen_row.createCell(17);
            Cell17.setCellValue(5);
            XSSFCell Cell18 = hatchpen_row.createCell(18);
            Cell18.setCellValue(5);
            XSSFCell Cell19 = hatchpen_row.createCell(19);
            Cell19.setCellValue(5);
            

            // create_by
            XSSFCell createByCell = hatchpen_row.createCell(20);
            createByCell.setCellValue("1");

            // create_date
            XSSFCell createDateCell = hatchpen_row.createCell(21);
            createDateCell.setCellValue(currentDateStr());

            // update_by
            XSSFCell updateByCell = hatchpen_row.createCell(22);
            updateByCell.setCellValue("1");

            // update_date
            XSSFCell updateDateCell = hatchpen_row.createCell(23);
            updateDateCell.setCellValue(currentDateStr());

            // remark
            XSSFCell remarkCell = hatchpen_row.createCell(24);
            remarkCell.setCellValue("期初导入");

            // del_flag
            XSSFCell delflagCell = hatchpen_row.createCell(25);
            delflagCell.setCellValue("0");
        }
        outputWorkbook(hatch_penPath,hatch_pen_wb);

    }

    /**
     * Utility: 获取workbook
     * @param path
     * @return
     */
    private static XSSFWorkbook getWorkbook(String path) {
    	log.info("getWorkbook方法。。。。。");
        File file = new File(path);
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * Utility: 写文件
     * @param path
     * @param wb
     */
    private static void outputWorkbook(String path,XSSFWorkbook wb) throws IOException {
    	log.info("outputWorkbook方法。。。。。");
    	FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(path);
            wb.write(outputStream);
            outputStream.close();
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * Utility: 返回当期时间字符串
     * @return
     */
    private static String currentDateStr(){
    	log.info("设置时间日期。。。。。。");
        SimpleDateFormat YMdHmFormat = new SimpleDateFormat("yyyy/MM/dd");
        return YMdHmFormat.format(new Date());
    }

}
